package sqladvisor

import (
	"fmt"
	"os"
	"regexp"
	"strings"

	"github.com/pingcap/errors"
	"github.com/romberli/go-util/constant"
	"github.com/romberli/go-util/linux"
	"github.com/romberli/go-util/middleware/sql/parser"
	"github.com/romberli/log"
	"github.com/spf13/viper"

	"github.com/romberli/das/config"
	"github.com/romberli/das/internal/app/metadata"
	"github.com/romberli/das/internal/dependency/sqladvisor"
)

const (
	tempPattern = "soar_temp_query.*"

	u001bCode  = "\u001b"
	u003cCode  = "\\u003c"
	u003eCode  = "\\u003e"
	crlfString = "\\n"
	tabString  = "\\t"

	u003cString = "<"
	u003eString = ">"

	logExp               = `^\d{4}/\d{2}/\d{2} \d{2}:\d{2}:\d{2}\.\d{3}`
	errExp               = `\[1;31m\[E]|\[1;31m\[F]`
	defaultLogMessageLen = 3

	caseStr = `"Case":`

	emptyAdvice = `{"message": "no advice"}`
)

var (
	_ sqladvisor.Advisor = (*DefaultAdvisor)(nil)

	repMap = map[string]string{
		u001bCode:           constant.EmptyString,
		u003cCode:           u003cString,
		u003eCode:           u003eString,
		constant.CRLFString: crlfString,
		constant.TabString:  tabString,
	}
)

type DefaultAdvisor struct {
	parser     *parser.Parser
	soarBin    string
	configFile string
}

// NewDefaultAdvisor returns a new sqladvisor.Advisor
func NewDefaultAdvisor(soarBin, configFile string) sqladvisor.Advisor {
	return newDefaultAdvisor(soarBin, configFile)
}

// NewDefaultAdvisorWithDefault returns a new sqladvisor.Advisor with default value
func NewDefaultAdvisorWithDefault() sqladvisor.Advisor {
	soarBin := viper.GetString(config.SQLAdvisorSoarBinKey)
	configFile := viper.GetString(config.SQLAdvisorSoarConfigKey)

	return newDefaultAdvisor(soarBin, configFile)
}

// newDefaultAdvisor returns a new *DefaultAdvisor
func newDefaultAdvisor(soarBin, configFile string) *DefaultAdvisor {
	return &DefaultAdvisor{
		parser:     parser.NewParserWithDefault(),
		soarBin:    soarBin,
		configFile: configFile,
	}
}

// GetParser returns the parser
func (da *DefaultAdvisor) GetParser() *parser.Parser {
	return da.parser
}

// GetFingerprint returns the fingerprint of the sql text
func (da *DefaultAdvisor) GetFingerprint(sqlText string) string {
	return da.GetParser().GetFingerprint(sqlText)
}

// GetSQLID returns the identity of the sql text
func (da *DefaultAdvisor) GetSQLID(sqlText string) string {
	return da.GetParser().GetSQLID(sqlText)
}

// AdviseByDBID parses the sql text with given db id, then returns the tuning advice,
// note that only the first sql statement in the sql text will be advised
func (da *DefaultAdvisor) AdviseByDBID(dbID int, sqlText string) (string, string, error) {
	log.Debugf("sqladvisor DefaultAdvisor.AdviseByDBID(): argument : db id: %d, sql text: %s", dbID, sqlText)
	user := viper.GetString(config.DBApplicationMySQLUserKey)
	pass := viper.GetString(config.DBApplicationMySQLPassKey)

	dsn, err := da.getOnlineDSN(dbID, user, pass)
	if err != nil {
		return constant.EmptyString, constant.EmptyString, err
	}

	command := fmt.Sprintf(`%s -config=%s -online-dsn=%s -query="%s"`, da.soarBin, da.configFile, dsn, sqlText)

	log.Debugf("sqladvisor DefaultAdvisor.AdviseByDBID(): command: %s", command)

	result, err := linux.ExecuteCommand(command)
	if err != nil {
		return constant.EmptyString, constant.EmptyString, err
	}

	return da.parseResult(result)
}

// AdviseByHostInfoAndDBName parses the sql text with given mysql server host info and db name, then returns the tuning advice,
// note that only the first sql statement in the sql text will be advised
func (da *DefaultAdvisor) AdviseByHostInfoAndDBName(hostIP string, portNum int, dbName, sqlText string) (string, string, error) {
	log.Debugf("sqladvisor DefaultAdvisor.AdviseByHostInfoAndDBName(): argument : host ip: %s, port num: %d, db name: %s, sql text: %s",
		hostIP, portNum, dbName, sqlText)
	// write sql text to temp file
	fileName, err := da.writeSQLTextToTempFile(sqlText)
	if err != nil {
		return constant.EmptyString, constant.EmptyString, err
	}
	defer func() {
		err = os.Remove(fileName)
		if err != nil {
			log.Errorf("sqladvisor DefaultAdvisor.AdviseByHostInfoAndDBName(): remove query temporary file failed. error:\n%+v", err)
		}
	}()
	// get dsn
	dsn := fmt.Sprintf("%s:%s@%s:%d/%s",
		viper.GetString(config.DBApplicationMySQLUserKey),
		viper.GetString(config.DBApplicationMySQLPassKey),
		hostIP, portNum, dbName)
	// get command
	command := fmt.Sprintf(`%s -config=%s -online-dsn=%s -query="%s"`, da.soarBin, da.configFile, dsn, fileName)

	log.Debugf("sqladvisor DefaultAdvisor.AdviseByHostInfoAndDBName(): command: %s", command)
	// execute command
	result, err := linux.ExecuteCommand(command)
	if err != nil {
		return constant.EmptyString, constant.EmptyString, err
	}
	// parse result
	return da.parseResult(result)
}

// writeSQLTextToTempFile writes the sql text to the temp file
func (da *DefaultAdvisor) writeSQLTextToTempFile(sqlText string) (string, error) {
	f, err := os.CreateTemp(viper.GetString(config.SQLAdvisorSoarTempDirKey), tempPattern)
	if err != nil {
		return constant.EmptyString, err
	}
	defer func() { _ = f.Close() }()

	_, err = f.WriteString(sqlText)
	if err != nil {
		return f.Name(), err
	}

	return f.Name(), nil
}

// getOnlineDSN returns the online dsn which will be used by soar
func (da *DefaultAdvisor) getOnlineDSN(dbID int, user, pass string) (string, error) {
	// get db service
	dbService := metadata.NewDBServiceWithDefault()
	err := dbService.GetByID(dbID)
	if err != nil {
		return constant.EmptyString, nil
	}
	// get db
	db := dbService.GetDBs()[constant.ZeroInt]
	clusterID := db.GetClusterID()
	dbName := db.GetDBName()
	// get mysql server
	mysqlServerService := metadata.NewMySQLServerServiceWithDefault()
	err = mysqlServerService.GetByClusterID(clusterID)
	if err != nil {
		return constant.EmptyString, err
	}
	mysqlServers := mysqlServerService.GetMySQLServers()
	if len(mysqlServers) == constant.ZeroInt {
		return constant.EmptyString, errors.Errorf("could not find mysql server of the database. db id: %d", dbID)
	}
	mysqlServer := mysqlServers[constant.ZeroInt]

	return fmt.Sprintf("%s:%s@%s:%d/%s", user, pass, mysqlServer.GetHostIP(), mysqlServer.GetPortNum(), dbName), nil
}

// parseResult parses result, it will split the advice information and the log information
func (da *DefaultAdvisor) parseResult(result string) (string, string, error) {
	var (
		advice  string
		message string
		errMsg  string
	)

	isLogMsg := true
	logExpression, err := regexp.Compile(logExp)
	if err != nil {
		return constant.EmptyString, constant.EmptyString, err
	}
	errExpression, err := regexp.Compile(errExp)
	if err != nil {
		return constant.EmptyString, constant.EmptyString, err
	}

	lines := strings.Split(result, constant.CRLFString)
	for _, line := range lines {
		// replace some characters
		for key, value := range repMap {
			line = strings.ReplaceAll(line, key, value)
		}

		if isLogMsg {
			isLogMsg = logExpression.Match([]byte(line))
		}

		if isLogMsg {
			message += line + constant.CRLFString
			stringList := strings.Split(line, constant.SpaceString)
			if len(stringList) >= defaultLogMessageLen {
				if errExpression.Match([]byte(stringList[2])) {
					errMsg += line + constant.CRLFString
				}
			}

			continue
		}

		if strings.Contains(line, caseStr) {
			line = strings.ReplaceAll(line, constant.CRLFString, constant.EmptyString)
		}
		advice += line
	}

	advice = strings.TrimSpace(strings.Trim(strings.Trim(advice, constant.LeftBracketString), constant.RightBracketString))
	message = strings.TrimSpace(message)

	if errMsg != constant.EmptyString {
		return advice, message, errors.Errorf("parse result failed. error:\n%s", errMsg)
	}

	if advice == constant.EmptyString {
		advice = emptyAdvice
	}

	return advice, message, nil
}
